﻿using Dapper;
using System;
using System.Data;
using System.Linq;
using System.Text;
using SilenthinkContract;
using SilenthinkContract.Domain;
using System.Collections.Generic;
using SilenthinkContract.Component;

namespace SilenthinkService
{
    public class StoreService : ServiceBase, IStoreService
    {
        public bool CreateProductCategory(ProductCategory o)
        {
            var builder = new SqlBuilder();
            builder.Table(Database.Store.Table.ProductCategory)
                .Field("(Name)VALUES(@Name)");
            builder.AddParameters(new { o.Name });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public IList<ProductCategory> GetProductCategories(bool? status = null)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Store.Table.ProductCategory);

            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status = status.Value });
            }

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Query<ProductCategory>(connection, template).ToList();
            }
        }

        public bool CreateProduct(Product o)
        {
            var result = false;
            var builderA = new SqlBuilder()
               .Table(Database.Store.Table.Product)
               .Field("([Name],[CategoryCode])VALUES(@Name,@CategoryCode)")
               .AddParameters(new { o.Name, o.CategoryCode });
            var templateA=builderA.AddTemplate(SqlTemplate.Insert);

            var extra = o.ProductExtra;
            var builderB = new SqlBuilder()
                  .Table(Database.Store.Table.ProductExtra)
                  .Field("([Id],[SeName])VALUES(@Id,@SeName)")
                  .AddParameters(new { extra.Id, extra.SeName });
            var templateB = builderB.AddTemplate(SqlTemplate.Insert);

            var builderC = new SqlBuilder()
                   .Table(Database.Store.Table.ProductVariant)
                   .Field("([ProductId],[VariantCode],[VariantPrice],[VariantQuantity],[CreatedBy],[CreatedOn])VALUES(@ProductId,@VariantCode,@VariantPrice,@VariantQuantity,@CreatedBy,@CreatedOn)");
            var templateC = builderC.AddTemplate(SqlTemplate.Insert);

            var builderD = new SqlBuilder()
                    .Table(Database.Store.Table.ProductAttribute)
                    .Field("([ProductId],[ProductCategoryCode],[AttributeNameCode],[AttributeValueCode],[CreatedBy],[CreatedOn])VALUES(@ProductId,@ProductCategoryCode,@AttributeNameCode,@AttributeValueCode,@CreatedBy,@CreatedOn)");
            var templateD = builderD.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    var productId = InsertReturnLastId(o, connection, templateA, transaction);
                    if (productId > 0)
                    {
                        o.Id = productId;
                        ProcessProductAttribute(o);
                        //处理产品扩展信息

                        var a = Execute(connection, templateB, transaction) == 1;

                        //添加产品变体

                        var b = BatchInsert(connection, templateC.RawSql, o.ProductVariants, transaction) > 0;

                        //添加产品属性
                        var c = true;
                        if (o.ProductAttributes.Any())
                        {
                            c = BatchInsert(connection, templateD.RawSql, o.ProductAttributes, transaction) > 0;
                        }
                        result = a && b && c;
                        if (!result)
                        {
                            transaction.Rollback();
                        }
                        else
                        {
                            transaction.Commit();
                        }
                    }
                }
                catch (Exception e)
                {
                    transaction.Rollback();
                    result = false;
                }
            }

            UpdateProductXmlAttribute(o);
            return result;
        }

        private static void ProcessProductAttribute(Product o)
        {
            o.ProductExtra.Id = o.Id;
            if (o.ProductAttributes.Any())
            {
                foreach (var attribute in o.ProductAttributes)
                {
                    attribute.ProductId = o.Id;
                }
            }
            foreach (var variant in o.ProductVariants)
            {
                variant.ProductId = o.Id;
            }
        }
        private void UpdateProductXmlAttribute(Product product)
        {
            List<ProductAttributeInfo> attributes;
            var template = GetProductAttributeNameValue(product.Id, product.CategoryCode, false);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                attributes = Query<ProductAttributeInfo>(connection, template).ToList();
            }
            if (attributes.Count > 0)
            {
                var xmlAttribute = new StringBuilder();
                xmlAttribute.Append("<attributes>");
                foreach (var o in attributes)
                {
                    xmlAttribute.AppendFormat(
                        "<attribute nameCode=\"{0}\" valueCode=\"{1}\" name=\"{2}\" value=\"{3}\"/>", o.NameCode,
                        o.ValueCode, o.Name, o.Value);
                }
                xmlAttribute.Append("</attributes>");
                product.XmlAttribute = xmlAttribute.ToString();
            }

            List<ProductAttributeInfo> saleAttributes;
            template = GetProductAttributeNameValue(product.Id, product.CategoryCode, true);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                saleAttributes = Query<ProductAttributeInfo>(connection, template).ToList();
            }
            if (saleAttributes.Count > 0)
            {
                var dic = saleAttributes.GroupBy(o => o.NameCode);
                var saleXmlAttribute = new StringBuilder();
                saleXmlAttribute.Append("<attributes>");
                foreach (var o in dic)
                {
                    var key = o.Key;
                    var firstValue = o.FirstOrDefault();
                    saleXmlAttribute.AppendFormat("<attribute code=\"{0}\" text=\"{1}\">", o.Key, firstValue.Name);
                    foreach (var v in saleAttributes.Where(x => x.ValueCode.StartsWith(key)))
                    {
                        saleXmlAttribute.AppendFormat("<value code=\"{0}\" text=\"{1}\"/>", v.ValueCode, v.Value);
                    }
                    saleXmlAttribute.Append("</attribute>");
                }
                saleXmlAttribute.Append("</attributes>");
                product.SaleXmlAttribute = saleXmlAttribute.ToString();
                template = InnerUpdateProductXmlAttribute(product);
                using (var connection = GetConnection(ConnectionName.Store))
                {
                    Execute(connection, template);
                }
            }
        }
        internal static SqlBuilder.Template InnerUpdateProductXmlAttribute(Product o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Store.Table.Product)
                .Field("[XmlAttribute]=@XmlAttribute", new { o.XmlAttribute })
                .Field("[SaleXmlAttribute]=@SaleXmlAttribute", new { o.SaleXmlAttribute })
                .Where("[Id]=@Id", new { o.Id });
            return builder.AddTemplate(SqlTemplate.Update);
        }
        internal static SqlBuilder.Template GetProductAttributeNameValue(long productId, string productCategoryCode, bool isSaleAttribute)
        {
            var builder = new SqlBuilder()
                .Where(isSaleAttribute ? "[AttributeType]=2" : "[AttributeType]<>2")
                .Where("attribute.ProductId=@ProductId", new { productId });
            if (productCategoryCode.Length > 2 && productCategoryCode.Length % 2 == 0)
            {
                var x = productCategoryCode.Length / 2;
                var y = new string[x];
                var length = 2;
                for (var i = 1; i <= x; i++)
                {
                    var temp = productCategoryCode.Substring(0, length);
                    y[i - 1] = temp;
                    length = (i + 1) * 2;
                }
                var str = y.Aggregate(string.Empty, (current, o) => current + ("'" + o + "',")).Trim(',');

                builder.Where("(name.ProductCategoryCode IN(" + str + ") AND value.ProductCategoryCode  IN(" + str + "))");
            }
            else
            {
                builder.Where(
                    "(name.ProductCategoryCode =@ProductCategoryCode AND value.ProductCategoryCode=@ProductCategoryCode)",
                    new { productCategoryCode });
            }

            return
                builder.AddTemplate(
                    "select attribute.AttributeNameCode AS NameCode,attribute.AttributeValueCode AS ValueCode,name.AttributeName As Name,value.AttributeValueName AS Value from [ProductAttribute] attribute inner join dbo.ProductAttributeName name on attribute.AttributeNameCode=name.AttributeNameCode inner join  dbo.ProductAttributeValue value on attribute.AttributeValueCode=value.AttributeValueCode /**where**/");
        }

        public Product GetProduct(long id, bool inclExtra = false, bool inclVariants = false)
        {
            Product o;
            var sb = new StringBuilder();
            sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE [Id]=@Id;", Database.Store.Table.Product);
            if (inclExtra)
            {
                sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE [Id]=@Id;",
                                Database.Store.Table.ProductExtra);
            }
            if (inclVariants)
            {
                sb.AppendFormat("SELECT * FROM {0} WHERE [ProductId]=@Id;",
                                Database.Store.Table.ProductVariant);
            }
            var template = new SqlBuilder().AddTemplate(sb.ToString(), new { id });
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            using (var multi =connection.QueryMultiple(template))
            {
                o = multi.Read<Product>().FirstOrDefault();
                if (o != null)
                {
                    if (inclExtra)
                    {
                        o.ProductExtra = multi.Read<ProductExtra>().FirstOrDefault();
                    }
                    if (inclVariants)
                    {
                        o.ProductVariants = multi.Read<ProductVariant>().ToList();
                    }
                }
            }
            return o;
        }


        public PageList<Product> GetPagedProductList(int size = 20, int index = 1, string name = "")
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Store.Table.Product)
                .OrderBy("id desc").AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

            if (!string.IsNullOrEmpty(name))
            {
                builder.Where("[Name]=@Name", new { name });
            }
            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return PageList<Product>(connection, template);
            }
        }

        public bool UpdateProduct(Product o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Store.Table.Product)
                .Field("[Name]=@Name", new { o.Name })
                .Where("[Id]=@Id AND [Version]=@Version", new { o.Id, o.Version });

            var template = builder.AddTemplate(SqlTemplate.Update);

            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteProduct(long id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Store.Table.Product)
                .Field("[Status]=@Status", new { Status = ProductStatus.Deleted })
                .Where("[Id]=@Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        #region ProductVariant
        //public ProductVariant GetProductVariant(long id, bool inclProduct = false, bool inclProductExtra = false)
        //{
        //    ProductVariant o;
        //    var template = productTemplate.GetProductVariant(id);
        //    using (var connection = GetConnection(ConnectionName.Store))
        //    {
        //        o = Query<ProductVariant>(connection, template).FirstOrDefault();
        //        if (o != null && inclProduct)
        //        {
        //            template = productTemplate.GetProduct(id);
        //            o.Product = Query<Product>(connection, template).FirstOrDefault();
        //            if (o.Product != null && inclProductExtra)
        //            {
        //                template = productTemplate.GetProductExtra(o.Product.Id);
        //                o.Product.ProductExtra = Query<ProductExtra>(connection, template).FirstOrDefault();
        //            }
        //        }
        //    }
        //    return o;
        //}

        public IList<ProductAttributeName> GetProductAttributeName(string productCategoryCode, string parentAttributeNameCode, int? id, bool? status)
        {
            IList<ProductAttributeName> result;
            var builder = new SqlBuilder().Field("*")
                   .Table(Database.Store.Table.ProductAttributeName)
                   .Where("[ParentAttributeNameCode]=@parentAttributeNameCode", new { parentAttributeNameCode });

            if (!string.IsNullOrEmpty(productCategoryCode))
            {
                if (productCategoryCode.Length > 2 && productCategoryCode.Length % 2 == 0)
                {
                    var x = productCategoryCode.Length / 2;
                    var y = new string[x];
                    var length = 2;
                    for (var i = 1; i <= x; i++)
                    {
                        var temp = productCategoryCode.Substring(0, length);
                        y[i - 1] = temp;
                        length = (i + 1) * 2;
                    }
                    var str = y.Aggregate(string.Empty, (current, o) => current + ("'" + o + "',")).Trim(',');

                    builder.Where("[ProductCategoryCode] IN(" + str + ")");
                }
                else
                {
                    builder.Where("[ProductCategoryCode]=@productCategoryCode", new { productCategoryCode });
                }
            }
            if (id.HasValue)
            {
                builder.Where("[Id]=@Id", new { id = id.Value });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status = status.Value });
            }

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                result = Query<ProductAttributeName>(connection, template).ToList();
            }
            return result;
        }

        public IList<ProductAttributeValue> GetProductAttributeValue(string productCategoryCode, bool byAttributeNameCode, string[] attributeNameCodeArray, int? id = null, bool? status = null)
        {
            IList<ProductAttributeValue> result;
            var builder = new SqlBuilder().Field("*")
                    .Table(Database.Store.Table.ProductAttributeValue);

            if (productCategoryCode.Length > 2 && productCategoryCode.Length % 2 == 0)
            {
                var x = productCategoryCode.Length / 2;
                var y = new string[x];
                var length = 2;
                for (var i = 1; i <= x; i++)
                {
                    var temp = productCategoryCode.Substring(0, length);
                    y[i - 1] = temp;
                    length = (i + 1) * 2;
                }
                var str = y.Aggregate(string.Empty, (current, o) => current + ("'" + o + "',")).Trim(',');

                builder.Where("[ProductCategoryCode] IN(" + str + ")");
            }
            else
            {
                builder.Where("[ProductCategoryCode]=@productCategoryCode", new { productCategoryCode });
            }

            if (byAttributeNameCode)
            {
                if (attributeNameCodeArray.Length > 0)
                {
                    var s = attributeNameCodeArray.Aggregate(string.Empty,
                                                             (current, o) => current + ("'" + o + "',"));
                    builder.Where(string.Format("[AttributeNameCode] IN ({0})", s.TrimEnd(',')));
                }
            }
            if (id.HasValue)
            {
                builder.Where("[Id]=@Id", new { id = id.Value });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status = status.Value });
            }

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                result = Query<ProductAttributeValue>(connection, template).ToList();
            }
            return result;
        }

        public bool CreateProductAttributeName(ProductAttributeName o)
        {
            var builder = new SqlBuilder()
                               .Table(Database.Store.Table.ProductAttributeName).Field(
                                   "([AttributeNameCode],[ParentAttributeNameCode],[ParentAttributeValueCode],[AttributeName],[AttributeNameDescription],[AttributeNameLevel],[AttributeType],[AttributeControlType],[IsMust],[ProductCategoryCode],[HasChild],[ChildAttributeNameCode],[DisplayOrder],[Status],[CreatedBy],[CreatedOn])VALUES(@AttributeNameCode,@ParentAttributeNameCode,@ParentAttributeValueCode,@AttributeName,@AttributeNameDescription,@AttributeNameLevel,@AttributeType,@AttributeControlType,@IsMust,@ProductCategoryCode,@HasChild,@ChildAttributeNameCode,@DisplayOrder,@Status,@CreatedBy,@CreatedOn)")
                               .AddParameters(new { o.AttributeNameCode, o.ParentAttributeNameCode, o.ParentAttributeValueCode, o.AttributeName, o.AttributeNameDescription, o.AttributeNameLevel, o.AttributeType, o.AttributeControlType, o.IsMust, o.ProductCategoryCode, o.HasChild, o.ChildAttributeNameCode, o.DisplayOrder, o.Status, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }

        }

        public bool CreateProductAttributeValue(ProductAttributeValue o)
        {
            var builder = new SqlBuilder()
                   .Table(Database.Store.Table.ProductAttributeValue)
                   .Field("([AttributeValueCode],[AttributeValueName],[AttributeValueDescription],[AttributeNameCode],[ProductCategoryCode],[PreSelected],[DisplayOrder],[Status],[CreatedBy],[CreatedOn])VALUES(@AttributeValueCode,@AttributeValueName,@AttributeValueDescription,@AttributeNameCode,@ProductCategoryCode,@PreSelected,@DisplayOrder,@Status,@CreatedBy,@CreatedOn)")
                   .AddParameters(new { o.AttributeValueCode, o.AttributeValueName, o.AttributeValueDescription, o.AttributeNameCode, o.ProductCategoryCode, o.PreSelected, o.DisplayOrder, o.Status, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }



        #endregion

        #region Discount

        public bool CreateDiscount(Discount o)
        {
            var builder = new SqlBuilder()
                    .Table(Database.Store.Table.Discount)
                    .Field("([Name],[AssignedTo],[UsePercentage],[Percentage],[Amount],[StartDate],[EndDate],[Limitation],[LimitationTimes],[Status],[CreatedBy])" +
                        "VALUES(@Name,@AssignedTo,@UsePercentage,@Percentage,@Amount,@StartDate,@EndDate,@Limitation,@LimitationTimes,@Status,@CreatedBy)")
                    .AddParameters(new {o.Name,o.AssignedTo,o.UsePercentage,o.Percentage,o.Amount,o.StartDate,o.EndDate,o.Limitation,o.LimitationTimes,o.CreatedBy});

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public Discount GetDiscount(int id)
        {
            Discount o;
            var sb = new StringBuilder();
            sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE ID=@Id;", Database.Store.Table.Discount);
            sb.AppendFormat("SELECT * FROM {0} WHERE DiscountId=@Id;",Database.Store.Table.DiscountRequirement);
            var template = new SqlBuilder().AddTemplate(sb.ToString(), new { id });
            using (var connection = GetConnection(ConnectionName.Store))
            using (var multi = connection.QueryMultiple(template))
            {
                o = multi.Read<Discount>().FirstOrDefault();
                if (o != null)
                {
                    o.DiscountRequirements = multi.Read<DiscountRequirement>().ToList();
                }
            }
            return o;
        }

        public PageList<Discount> GetPagedDiscount(int size = 20, int index = 1, DiscountAssignedTo? assignedTo = null, bool? usePercentage = false, bool? status = false)
        {
            PageList<Discount> o;
            var builder = new SqlBuilder()
                    .Field("*")
                    .Table(Database.Store.Table.Discount)
                    .OrderBy("id desc");
            if (assignedTo.HasValue)
            {
                builder.Where("[AssignedTo]=@AssignedTo", new { assignedTo });
            }
            if (usePercentage.HasValue)
            {
                builder.Where("[UsePercentage]=@UsePercentage", new { usePercentage });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status });
            }
            builder.AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                o = PageList<Discount>(connection, template);
            }
            return o;
        }

        public IList<DiscountRequirement> GetDiscountRequirement(int discountId)
        {
            IList<DiscountRequirement> result;
            using (var connection = GetConnection(ConnectionName.Store))
            {
                result = GetDiscountRequirement(connection, discountId);
            }
            return result;
        }

        public IList<Discount> GetAvailableDiscount()
        {
            IList<Discount> o = new List<Discount>();
            var builder = new SqlBuilder()
                   .Field("*")
                   .Table(Database.Store.Table.Discount)
                   .Where("[Status]=1");

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                var temp = Query<Discount>(connection, template).ToList();
                foreach (var t in temp)
                {
                    var startTime = t.StartDate.HasValue ? t.StartDate.Value : new DateTime(2010, 1, 1);
                    var endTime = t.EndDate.HasValue ? t.EndDate.Value : DateTime.MaxValue;
                    if (DateTime.Now >= startTime && DateTime.Now <= endTime)
                    {
                        o.Add(t);
                    }
                }

                if (o.Any() && o.Count > 0)
                {
                    foreach (var discount in o)
                    {
                        var requirements = GetDiscountRequirement(connection, discount.Id);
                        discount.DiscountRequirements = requirements;
                        switch (discount.AssignedTo)
                        {
                            case DiscountAssignedTo.Category:
                                template = GetDiscountAssignedTo(DiscountAssignedTo.Category, discount.Id);
                                discount.AppliedToCategories = Query<string>(connection, template).ToList();
                                break;
                            case DiscountAssignedTo.Productvariant:
                                template = GetDiscountAssignedTo(DiscountAssignedTo.Productvariant, discount.Id);
                                discount.AppliedToProductVariants = Query<Guid>(connection, template).ToList();
                                break;
                        }
                    }
                }
            }
            return o;
        }
        internal static SqlBuilder.Template GetDiscountAssignedTo(DiscountAssignedTo assignedTo, int discountId)
        {
            var builder = new SqlBuilder();
            switch (assignedTo)
            {
                case DiscountAssignedTo.Category:
                    builder.Field("[CategoryCode]").Table(
                        Database.Store.Table.DiscountProductCategoryMapping);
                    break;
                case DiscountAssignedTo.Productvariant:
                    builder.Field("[VariantId]").Table(
                        Database.Store.Table.DiscountProductVariantMapping);
                    break;
            }
            builder.Where("[Status]=1 AND [DiscountId]=@DiscountId", new { discountId });
            return builder.AddTemplate(SqlTemplate.Select);
        }

        public bool ValidateDiscount(int discountId, int limitationTimes, long? userId)
        {
            var builder = new SqlBuilder()
                .Field("Count(0) as Count")
                .Table(Database.Store.Table.DiscountUsageHistory)
                .Where("[DiscountId]=@DiscountId", new {discountId});
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId]=@UserId", new {userId});
            }

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Query<int>(connection, template).First() <= limitationTimes;
            }
        }

        public bool CreateDiscountRequirement(DiscountRequirement o)
        {
            var builder = new SqlBuilder()
                   .Table(Database.Store.Table.Discount)
                   .Field(
                       "([DiscountId],[RequirementRule],[SpentAmount],[BillingAreaCodes],[ShippingAreaCodes],[UserRoleIds],[ProductVariantIds],[CreatedBy])" +
                       "VALUES(@DiscountId,@RequirementRule,@SpentAmount,@BillingAreaCodes,@ShippingAreaCodes,@UserRoleIds,@ProductVariantIds,@CreatedBy)")
                   .AddParameters(
                       new {
                           o.DiscountId,
                           o.RequirementRule,
                           o.SpentAmount,
                           o.BillingAreaCodes,
                           o.ShippingAreaCodes,
                           o.UserRoleIds,
                           o.ProductVariantIds,
                           o.CreatedBy
                       });
            
            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        private static IList<DiscountRequirement> GetDiscountRequirement(IDbConnection connection, int discountId)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Store.Table.DiscountRequirement)
                .Where("[DiscountId]=@DiscountId", new {discountId});

            var template = builder.AddTemplate(SqlTemplate.Select);
            return Query<DiscountRequirement>(connection, template).ToList();
        }

        #endregion


        
        #region ProductAdvisory
        public bool CreateProductAdvisory(ProductAdvisory o)
        {
            var builder = new SqlBuilder()
            .Table(Database.Store.Table.ProductAdvisory)
            .Field("([Category],[ProductId],[UserId],[NickName],[Question],[Answer],[Answerer],[AnswerTime],[Satisfaction],[DisSatisfaction],[Status],[CreatedBy],[CreatedOn])VALUES(@Category,@ProductId,@UserId,@NickName,@Question,@Answer,@Answerer,@AnswerTime,@Satisfaction,@DisSatisfaction,@Status,@CreatedBy,@CreatedOn)")
            .AddParameters(new { o.Category, o.ProductId, o.UserId, o.NickName, o.Question, o.Answer, o.Answerer, o.AnswerTime, o.Satisfaction, o.DisSatisfaction, o.Status, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateProductAdvisory(ProductAdvisory o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Store.Table.ProductAdvisory)
                .Field("[Category]=@Category", new {o.Category})
                .Field("[ProductId]=@ProductId", new {o.ProductId})
                .Field("[UserId]=@UserId", new {o.UserId})
                .Field("[NickName]=@NickName", new {o.NickName})
                .Field("[Question]=@Question", new {o.Question})
                .Field("[Answer]=@Answer", new {o.Answer})
                .Field("[Answerer]=@Answerer", new {o.Answerer})
                .Field("[AnswerTime]=@AnswerTime", new {o.AnswerTime})
                .Field("[Satisfaction]=@Satisfaction", new {o.Satisfaction})
                .Field("[DisSatisfaction]=@DisSatisfaction", new {o.DisSatisfaction})
                .Field("[Status]=@Status", new {o.Status})
                .Where("[Id] = @Id", new {o.Id});

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteProductAdvisory(long id)
        {
            var builder = new SqlBuilder()
              .Table(Database.Store.Table.ProductAdvisory)
              .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public ProductAdvisory GetProductAdvisory(long id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Store.Table.ProductAdvisory)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Query<ProductAdvisory>(connection, template).FirstOrDefault();
            }
        }

        public PageList<ProductAdvisory> GetPagedProductAdvisory(int index = 1, int size = 20, long? productId = null, int? userId = null, string createdBy = null, DateTime? createdOn = null, DateTime? answerTime = null, ProductAdvisoryCategory? category = null, ProductAdvisoryStatus? status = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Store.Table.ProductAdvisory)
                .OrderBy("id desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

            if (productId.HasValue && productId.Value > 0)
            {
                builder.Where("[ProductId=@ProductId", new { productId });
            }
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId=@UserId", new { userId });
            }
            if (!string.IsNullOrEmpty(createdBy))
            {
                builder.Where("[CreatedBy=@CreatedBy", new { createdBy });
            }
            if (createdOn.HasValue)
            {
                var createdOnBegin = new DateTime(createdOn.Value.Year, createdOn.Value.Month, createdOn.Value.Day);
                var createdOnEnd = new DateTime(createdOn.Value.Year, createdOn.Value.Month, createdOn.Value.Day).AddDays(1);
                builder.Where("[CreatedOn>=@CreatedOnBegin AND [CreatedOn<@CreatedOnEnd", new { createdOnBegin, createdOnEnd });
            }
            if (answerTime.HasValue)
            {
                var answerTimeBegin = new DateTime(answerTime.Value.Year, answerTime.Value.Month, answerTime.Value.Day);
                var answerTimeEnd = new DateTime(answerTime.Value.Year, answerTime.Value.Month, answerTime.Value.Day).AddDays(1);
                builder.Where("[AnswerTime>=@AnswerTimeBegin AND [AnswerTime<@AnswerTimeEnd", new { answerTimeBegin, answerTimeEnd });
            }
            if (category.HasValue && category.Value > 0)
            {
                builder.Where("[Category=@Category", new { category });
            }
            if (status.HasValue && status.Value > 0)
            {
                builder.Where("[Status=@Status", new { status });
            }

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return PageList<ProductAdvisory>(connection, template);
            }
        }
        #endregion

        #region ProductReview
        public bool CreateProductReview(ProductReview o)
        {
            var builder = new SqlBuilder()
            .Table(Database.Store.Table.ProductReview)
            .Field("([ProductId],[Satisfaction],[Title],[Advantage],[Shortage],[Experience],[Useful],[UserId],[NickName],[Status],[CreatedBy],[CreatedOn])VALUES(@ProductId,@Satisfaction,@Title,@Advantage,@Shortage,@Experience,@Useful,@UserId,@NickName,@Status,@CreatedBy,@CreatedOn)")
            .AddParameters(new { o.ProductId, o.Satisfaction, o.Title, o.Advantage, o.Shortage, o.Experience, o.Useful, o.UserId, o.NickName, o.Status, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateProductReview(ProductReview o)
        {
            var builder = new SqlBuilder()
                    .Table(Database.Store.Table.ProductReview)
                    .Field("[ProductId]=@ProductId", new { o.ProductId })
                    .Field("[Satisfaction]=@Satisfaction", new { o.Satisfaction })
                    .Field("[Title]=@Title", new { o.Title })
                    .Field("[Advantage]=@Advantage", new { o.Advantage })
                    .Field("[Shortage]=@Shortage", new { o.Shortage })
                    .Field("[Experience]=@Experience", new { o.Experience })
                    .Field("[Useful]=@Useful", new { o.Useful })
                    .Field("[UserId]=@UserId", new { o.UserId })
                    .Field("[NickName]=@NickName", new { o.NickName })
                    .Field("[Status]=@Status", new { o.Status })
                    .Where("[Id] = @Id", new { o.Id });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteProductReview(long id)
        {
            var builder = new SqlBuilder()
               .Table(Database.Store.Table.ProductReview)
               .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Execute(connection, template) == 1;
            }
        }

        public ProductReview GetProductReview(long id)
        {
            var builder = new SqlBuilder()
              .Field("*")
              .Table(Database.Store.Table.ProductReview)
              .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return Query<ProductReview>(connection, template).FirstOrDefault();
            }
        }

        public PageList<ProductReview> GetPagedProductReview(int index = 1, int size = 20, long? productId = null,
                                                          int? userId = null, string createdBy = null,
                                                          DateTime? createdOn = null,
                                                          ProductReviewStatus? status = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Store.Table.ProductReview)
                .OrderBy("id desc")
                .AddParameters(new {begin = (index - 1)*size + 1, end = index*size});
            /*
            .Where("");
            */

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return PageList<ProductReview>(connection, template);
            }
        }

        #endregion
        
        #region Implementation of IService

        public int GetCachedSQLCount()
        {
            return SqlMapper.GetCachedSQLCount();
        }
        public List<Tuple<string, string, int>> GetCachedSQL()
        {
            return SqlMapper.GetCachedSQL().ToList();
        }
        public void PurgeQueryCache()
        {
            SqlMapper.PurgeQueryCache();
        }
        #endregion
    }
}